<?php

class m_report_asset extends CI_Model {
    //put your code here
    public function __construct() {
        parent::__construct();
    }
    
    function get_total_harga_beli_nilai_buku($params){
        $sql = "SELECT SUM(barang_harga_beli) AS total_harga_beli, 
                SUM(barang_nilai_buku) AS total_nilai_buku
                FROM barang 
                WHERE DATE_ADD(barang_tglbeli, INTERVAL barang_umurekonomis YEAR) >= NOW()
                AND (kategoribarang_cd = ".$params[0];
        if (!empty($params[1])){
           $sql .= " OR kategoribarang_cd = ".$params[1]; 
        }  
        $sql .= ")";
        if (!empty($params[2])){
           $sql .= " AND barang_kondisi = '".$params[2]."'"; 
        }
        $sql .= " ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
//        echo $this->db->last_query($query);exit();
        if ($query->num_rows() > 0) {
            $result = $query->row_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    
    /*
     * MEBELAIR & FURNITUR
     */
    
    function get_mebelair($params){
        $sql = "SELECT *,DATE_ADD(barang_tglbeli, INTERVAL barang_umurekonomis YEAR) AS tgl_berakhir,
                (barang_umurekonomis * 12) AS umur_manfaat, ROUND((100/barang_umurekonomis),2) AS tarif,
                ROUND(barang_harga_beli/(barang_umurekonomis * 12)) AS tarif_penyusutan,
                TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW()) AS waktu,
                (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31')))) AS akm_last_year,
                ((ROUND(barang_harga_beli/(barang_umurekonomis * 12))*TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW())) 
                - (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31'))))) AS penyusutan_thn
                FROM barang WHERE barang_kondisi = 'baik' 
                AND barang_umurekonomis = ".$params[1]." 
                AND kategoribarang_cd = ".$params[2]."
                ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
//        echo $this->db->last_query($query);exit();
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    /*
     * MESIN & TOWER
     */
    
    function get_mesin($params){
        $sql = "SELECT *,DATE_ADD(barang_tglbeli, INTERVAL barang_umurekonomis YEAR) AS tgl_berakhir,
                (barang_umurekonomis * 12) AS umur_manfaat, ROUND((100/barang_umurekonomis),2) AS tarif,
                ROUND(barang_harga_beli/(barang_umurekonomis * 12)) AS tarif_penyusutan,
                TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW()) AS waktu,
                (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31')))) AS akm_last_year,
                ((ROUND(barang_harga_beli/(barang_umurekonomis * 12))*TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW())) 
                - (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31'))))) AS penyusutan_thn
                FROM barang WHERE barang_kondisi = 'baik' 
                AND barang_umurekonomis = ".$params[1]." 
                AND kategoribarang_cd = ".$params[2]."
                ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    /*
     * PERLENGKAPAN KANTOR & PERKULIAHAN
     */
    
    function get_perlengkapan($params){
        $sql = "SELECT *,DATE_ADD(barang_tglbeli, INTERVAL barang_umurekonomis YEAR) AS tgl_berakhir,
                (barang_umurekonomis * 12) AS umur_manfaat, ROUND((100/barang_umurekonomis),2) AS tarif,
                ROUND(barang_harga_beli/(barang_umurekonomis * 12)) AS tarif_penyusutan,
                TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW()) AS waktu,
                (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31')))) AS akm_last_year,
                ((ROUND(barang_harga_beli/(barang_umurekonomis * 12))*TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW())) 
                - (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31'))))) AS penyusutan_thn
                FROM barang WHERE barang_kondisi = 'baik' 
                AND barang_umurekonomis = ".$params[1]." 
                AND ( kategoribarang_cd = ".$params[2]." OR kategoribarang_cd = ".$params[3]." )
                ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    /*
     * KENDARAAN
     */
    
    function get_kendaraan($params){
        $sql = "SELECT *,DATE_ADD(barang_tglbeli, INTERVAL barang_umurekonomis YEAR) AS tgl_berakhir,
                (barang_umurekonomis * 12) AS umur_manfaat, ROUND((100/barang_umurekonomis),2) AS tarif,
                ROUND(barang_harga_beli/(barang_umurekonomis * 12)) AS tarif_penyusutan,
                TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW()) AS waktu,
                (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31')))) AS akm_last_year,
                ((ROUND(barang_harga_beli/(barang_umurekonomis * 12))*TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW())) 
                - (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31'))))) AS penyusutan_thn
                FROM barang WHERE barang_kondisi = 'baik' 
                AND barang_umurekonomis = ".$params[1]." 
                AND kategoribarang_cd = ".$params[2]."
                ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    /*
     * BANGUNAN
     */
    
    function get_bangunan($params){
        $sql = "SELECT *,DATE_ADD(barang_tglbeli, INTERVAL barang_umurekonomis YEAR) AS tgl_berakhir,
                (barang_umurekonomis * 12) AS umur_manfaat, ROUND((100/barang_umurekonomis),2) AS tarif,
                ROUND(barang_harga_beli/(barang_umurekonomis * 12)) AS tarif_penyusutan,
                TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW()) AS waktu,
                (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31')))) AS akm_last_year,
                ((ROUND(barang_harga_beli/(barang_umurekonomis * 12))*TIMESTAMPDIFF(MONTH, barang_tglbeli, NOW())) 
                - (ROUND(barang_harga_beli/(barang_umurekonomis * 12))*(TIMESTAMPDIFF(MONTH, barang_tglbeli, CONCAT(YEAR('".$params[0]."')-1,'-12-31'))))) AS penyusutan_thn
                FROM barang WHERE barang_kondisi = 'baik' 
                AND barang_umurekonomis = ".$params[1]." 
                AND kategoribarang_cd = ".$params[2]."
                ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
    
    /*
     * TANAH
     */
    
    function get_tanah($params){
        $sql = "SELECT * FROM barang WHERE kategoribarang_cd = ".$params[0]."
                ORDER BY barang_nama ASC";
        $query = $this->db->query($sql, $params);
        if ($query->num_rows() > 0) {
            $result = $query->result_array();
            $query->free_result();
            return $result;
        } else {
            return array();
        }
    }
}

